package com.wust.modules.online.config.service.impl;

import com.wust.modules.online.config.service.DbTableHandle;
import com.wust.modules.online.config.util.ColumnMeta;
import lombok.NoArgsConstructor;
import com.wust.common.util.oConvertUtils;

/**
 * @author wanheng
 */
@NoArgsConstructor
public class SqlServerDbTableHandle implements DbTableHandle {


    @Override
    public String getAddColumnSql(ColumnMeta columnMeta) {
        return " ADD  " + this.getCreateSql(columnMeta) + ";";
    }

    @Override
    public String getReNameFieldName(ColumnMeta columnMeta) {
        return "  sp_rename '" + columnMeta.getTableName() + "." + columnMeta.getOldColumnName() + "', '" + columnMeta.getColumnName() + "', 'COLUMN';";
    }

    @Override
    public String getUpdateColumnSql(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) {
        return " ALTER COLUMN  " + this.getCreateSql(cgformcolumnMeta, datacolumnMeta) + ";";
    }

    @Override
    public String getMatchClassTypeByDataType(String dataType, int digits) {
        String s = "";
        if (!"varchar".equalsIgnoreCase(dataType) && !"nvarchar".equalsIgnoreCase(dataType)) {
            if ("float".equalsIgnoreCase(dataType)) {
                s = "double";
            } else if ("int".equalsIgnoreCase(dataType)) {
                s = "int";
            } else if ("Date".equalsIgnoreCase(dataType)) {
                s = "date";
            } else if ("Datetime".equalsIgnoreCase(dataType)) {
                s = "date";
            } else if ("numeric".equalsIgnoreCase(dataType)) {
                s = "bigdecimal";
            } else if (!"varbinary".equalsIgnoreCase(dataType) && !"image".equalsIgnoreCase(dataType)) {
                if ("text".equalsIgnoreCase(dataType) || "ntext".equalsIgnoreCase(dataType)) {
                    s = "text";
                }
            } else {
                s = "blob";
            }
        } else {
            s = "string";
        }

        return s;
    }

    @Override
    public String dropTableSql(String tableName) {
        return " DROP TABLE " + tableName + " ;";
    }

    @Override
    public String getDropColumnSql(String fieldName) {
        return " DROP COLUMN " + fieldName + ";";
    }

    private String getCreateSql(ColumnMeta columnMeta, ColumnMeta columnMeta2) {
        String sql = "";
        if ("string".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " nvarchar(" + columnMeta.getColumnSize() + ") " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("date".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " datetime " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("int".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " int " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("double".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " float " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("bigdecimal".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " numeric(" + columnMeta.getColumnSize() + "," + columnMeta.getDecimalDigits() + ") " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("text".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " ntext " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("blob".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " image";
        }

        return sql;
    }

    private String getCreateSql(ColumnMeta columnMeta) {
        String sql = "";
        if ("string".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " nvarchar(" + columnMeta.getColumnSize() + ") " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("date".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " datetime " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("int".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " int " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("double".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " float " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("bigdecimal".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " numeric(" + columnMeta.getColumnSize() + "," + columnMeta.getDecimalDigits() + ") " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("text".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " ntext " + ("Y".equals(columnMeta.getIsNullable()) ? "NULL" : "NOT NULL");
        } else if ("blob".equalsIgnoreCase(columnMeta.getColunmType())) {
            sql = columnMeta.getColumnName() + " image";
        }

        return sql;
    }

    @Override
    public String getCommentSql(ColumnMeta columnMeta) {
        StringBuilder stringBuilder = new StringBuilder("EXECUTE ");
        if (oConvertUtils.isEmpty(columnMeta.getOldColumnName())) {
            stringBuilder.append("sp_addextendedproperty");
        } else {
            stringBuilder.append("sp_updateextendedproperty");
        }

        stringBuilder.append(" N'MS_Description', '");
        stringBuilder.append(columnMeta.getComment());
        stringBuilder.append("', N'SCHEMA', N'dbo', N'TABLE', N'");
        stringBuilder.append(columnMeta.getTableName());
        stringBuilder.append("', N'COLUMN', N'");
        stringBuilder.append(columnMeta.getColumnName()).append("'");
        return stringBuilder.toString();
    }

    @Override
    public String getSpecialHandle(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) {
        return null;
    }

    @Override
    public String dropIndexs(String indexName, String tableName) {
        return "DROP INDEX " + indexName + " ON " + tableName;
    }

    @Override
    public String countIndex(String indexName, String tableName) {
        return "SELECT count(*) FROM sys.indexes WHERE object_id=OBJECT_ID('" + tableName + "') and NAME= '" + indexName + "'";
    }
}
